#!/usr/bin/perl

use strict;
use POSIX;
use DBI;

# database details
my $dsn="dbi:Pg:dbname=atca;host=mentok;port=5432";
my $user="postgres";
my $password="";

# Setup the database connection
my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 0, AutoCommit => 1});

# we go through the database and get all the C1730 fluxes
# will output a sorted table to the screen
# just run this program with no arguments

# first, get all the data
my %data_c1730;
my $getdata_query="SELECT fluxdata.id,fluxdata.source,time,data_freq,tripleamp_value,".
    "tripleamp_scatter_measured,closurephase_measured FROM fluxdata INNER JOIN ".
    "observation ON (observation.id=ANY(fluxdata.observation_ids)) WHERE ".
    "project_code='C1730' AND reduced_auto=FALSE ORDER BY time";
my $getdata_handle=$dbh->prepare($getdata_query);
$getdata_handle->execute;
while (my @getdata_response=$getdata_handle->fetchrow()){
    if (!$data_c1730{'id_'.$getdata_response[0]}){
	$data_c1730{'id_'.$getdata_response[0]}={};
	my $ndata=$data_c1730{'id_'.$getdata_response[0]};
	$ndata->{'source'}=$getdata_response[1];
	$ndata->{'time'}=$getdata_response[2];
	$ndata->{'freq'}=&round(${$getdata_response[3]}[0]*100)/100;
	$ndata->{'flux'}=${$getdata_response[4]}[0];
	$ndata->{'unc'}=${$getdata_response[5]}[0];
	$ndata->{'defect'}=${$getdata_response[6]}[0];
    }
}
$getdata_handle->finish;

# get the list of all sources
my @usources;
foreach my $idkey (keys %data_c1730) {
    my $toadd=1;
    for (my $i=0;$i<=$#usources;$i++){
	if ($usources[$i] eq $data_c1730{$idkey}->{'source'}){
	    $toadd=0;
	    last;
	}
    }
    if ($toadd==1){
	push @usources,$data_c1730{$idkey}->{'source'};
    }
}

my @sources=sort @usources;

# get the list of all frequencies
my @frequencies;
foreach my $idkey (keys %data_c1730) {
    my $toadd=1;
    for (my $i=0;$i<=$#frequencies;$i++){
	if ($frequencies[$i]==$data_c1730{$idkey}->{'freq'}){
	    $toadd=0;
	    last;
	}
    }
    if ($toadd==1){
	push @frequencies,$data_c1730{$idkey}->{'freq'};
    }
}

my @sfrequencies = sort {$a <=> $b} @frequencies;

for (my $i=0;$i<=$#sources;$i++){
    printf "%12s   %10s",$sources[$i],'';
    for (my $j=0;$j<=$#sfrequencies;$j++){
	printf " | %16s",$sfrequencies[$j];
    }
    printf "\n";
    printf "-------------------------";
    for (my $j=0;$j<=$#sfrequencies;$j++){
	printf "-+-----------------";
    }
    printf "\n";
    my @ttimes;
    my @tfrequencies;
    my @tfluxes;
    foreach my $idkey (keys %data_c1730){
	if ($data_c1730{$idkey}->{'source'} eq $sources[$i]){
	    push @ttimes,$data_c1730{$idkey}->{'time'};
	    push @tfrequencies,$data_c1730{$idkey}->{'freq'};
	    push @tfluxes,$data_c1730{$idkey}->{'flux'}."+/-".
		$data_c1730{$idkey}->{'unc'};
	}
    }
    my @theselines;
    for (my $j=0;$j<=$#ttimes;$j++){
	my $thisline.=sprintf "%12s   %10s","",substr($ttimes[$j],0,10);
	my @ufrequencies;
	my @ufluxes;
	push @ufrequencies,$tfrequencies[$j];
	push @ufluxes,$tfluxes[$j];
	for (my $k=$j+1;$k<=$#ttimes;$k++){
	    if (substr($ttimes[$j],0,10) eq substr($ttimes[$k],0,10)){
		push @ufrequencies,splice @tfrequencies,$k,1;
		push @ufluxes,splice @tfluxes,$k,1;
		splice @ttimes,$k,1;
		$k--;
	    }
	}
	for (my $k=0;$k<=$#sfrequencies;$k++){
	    my $oflux="";
	    for (my $l=0;$l<=$#ufrequencies;$l++){
		if ($ufrequencies[$l]==$sfrequencies[$k]){
		    $oflux=$ufluxes[$l];
		}
	    }
	    $thisline.=sprintf " | %16s",$oflux;
	}
	$thisline.=sprintf "\n";
	push @theselines,$thisline;
    }
    my @sortedlines=sort @theselines;
    for (my $j=0;$j<=$#sortedlines;$j++){
	printf $sortedlines[$j];
    }
    printf "\n";
}

sub round {
    my $n=shift;

    return floor($n + 0.5);
}
